/*
	Author		:Srikanth
	Description	:This Procedure Calculates the inventory that can be expected in next 8 days from today
	Usage		:EXEC sp_show_expected_inventory_by_process 'ASSEMBLY', '0' ,'0','0'
	Note		:Added for Fab,Sort,Assembly and Test need to add to Inventory Loc deponding on the client info 
			 Currently for inventory loc we are not having expected date out.
*/

CREATE PROC sp_show_expected_inventory_by_process 

@PROCESS_TYPE		VARCHAR(20),
@PARTNUMBER		VARCHAR(1000),
@VENDOR			VARCHAR(20),
@DEFAULT_PROMPT		VARCHAR(20)

AS
BEGIN
	--RESULT TABLE TO STORE THE INVENTORY BETWEEN THE START DATE AND END DATES.
	DECLARE @FINAL_RESULT_TABLE Table (   	[QTY IN]		INT,
						[START DATE]		DATETIME,
						[END DATE]		DATETIME )

	--TEMP TABLE TO STORE THE INVENTORY OF THE PROCESS SELECTED.
	Create Table #RESULT_TABLE(     PART_NUMBER 		VARCHAR(50), 
					PROCESSSTEP_ID 		BIGINT,
					EXPECTED_DATE_OUT	DATETIME,
					QTY_IN			INT,
					LOCATION		VARCHAR(50),
					PROCESS			VARCHAR(20),
					PRODUCT_TYPE_ID 	INT )
	
	
	DECLARE @NOOFDAYS	INT-- VARIABLE TO STORE THE NUMBER OF DAYS INVENTORY TO BE CALULATED.
	DECLARE @QTY_IN		INT
	DECLARE @STARTDATE	DATETIME
	DECLARE @ENDDATE	DATETIME
	DECLARE @SQLSTRING	NVARCHAR(3000)

	--RETREVING TEST INFORMATION FROM FAB LOT.
	IF (@PROCESS_TYPE = 'FAB' )
	BEGIN	
		SET @SQLSTRING = N'INSERT INTO #RESULT_TABLE SELECT  P.PART_NUMBER,LT.PROCESSSTEP_ID, FL_EXPECTED_DATE_OUT,
			FL_INPUT_QUANTITY, L.L_VENDOR_NAME,''FAB'' AS PROCESS,CASE	
									  WHEN P_TYPE IS NULL   THEN 1
									  WHEN P_TYPE = ''BOARD'' THEN 2 
									  WHEN P_TYPE = ''KIT''   THEN 3 
									 END AS PRODUCT_TYPE_ID 
		FROM FAB_LOT FL,LOT_TABLE LT,PRODUCT P, LOCATION L 
		WHERE
		FL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID
		AND LOT_CURRENT_STATUS = ''RUNNING'' AND FL_EXPECTED_DATE_OUT IS NOT NULL'
	END	
	--RETREVING TEST INFORMATION FROM SORT LOT.
	ELSE IF (@PROCESS_TYPE = 'SORT' )
	BEGIN	
		SET @SQLSTRING = N'INSERT INTO #RESULT_TABLE SELECT  P.PART_NUMBER,LT.PROCESSSTEP_ID, SL_EXPECTED_DATE_OUT,--SL_QTY_OUT
			SL_WAFER_QTY, L.L_VENDOR_NAME,''FAB'' AS PROCESS,CASE	
									  WHEN P_TYPE IS NULL   THEN 1
									  WHEN P_TYPE = ''BOARD'' THEN 2 
									  WHEN P_TYPE = ''KIT''   THEN 3 
									 END AS PRODUCT_TYPE_ID 
		FROM SORT_LOT SL,LOT_TABLE LT,PRODUCT P, LOCATION L 
		WHERE
		SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID
		AND LOT_CURRENT_STATUS = ''RUNNING'' AND SL_EXPECTED_DATE_OUT IS NOT NULL'
	END	
	--RETREVING TEST INFORMATION FROM ASSEMBLY LOT.					
	ELSE IF (@PROCESS_TYPE = 'ASSEMBLY' )
	BEGIN	
		SET @SQLSTRING = N'INSERT INTO #RESULT_TABLE SELECT  P.PART_NUMBER,LT.PROCESSSTEP_ID, AL_EXPECTED_DATE_OUT,
			AL_QTY_IN, L.L_VENDOR_NAME,''ASSEMBLY'' AS PROCESS,CASE	
									  WHEN P_TYPE IS NULL   THEN 1
									  WHEN P_TYPE = ''BOARD'' THEN 2 
									  WHEN P_TYPE = ''KIT''   THEN 3 
									 END AS PRODUCT_TYPE_ID 
		FROM ASSEMBLY_LOT AL,LOT_TABLE LT,PRODUCT P, LOCATION L 
		WHERE
		AL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID
		AND LOT_CURRENT_STATUS = ''RUNNING'' AND AL_EXPECTED_DATE_OUT IS NOT NULL'		
	END
	--RETREVING TEST INFORMATION FROM FINAL TEST LOT.
	ELSE IF (@PROCESS_TYPE = 'TEST' )
	BEGIN	
		SET @SQLSTRING = N'INSERT INTO #RESULT_TABLE SELECT  P.PART_NUMBER,LT.PROCESSSTEP_ID, FTL_EXPECTED_DATE,
				FTL_QTY_IN, L.L_VENDOR_NAME,''TEST'' AS PROCESS,CASE	
									  		WHEN P_TYPE IS NULL   THEN 1
									  		WHEN P_TYPE = ''BOARD'' THEN 2 
									  		WHEN P_TYPE = ''KIT''   THEN 3 
										   END AS PRODUCT_TYPE_ID 
				FROM FINAL_TEST_LOT FTL,LOT_TABLE LT,PRODUCT P, LOCATION L 
				WHERE
				FTL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID
				AND LOT_CURRENT_STATUS = ''RUNNING'' AND FTL_EXPECTED_DATE IS NOT NULL'
	END
		
	IF @PARTNUMBER <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P.PROCESSSTEP_ID IN ( ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( ''' + @PARTNUMBER + ''') )  )'
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L_VENDOR_NAME = ''' +  @VENDOR + ''''
	END
	
	--PRINT @SQLSTRING

	EXEC( @SQLSTRING )
	
	SET @NOOFDAYS = 1
	WHILE @NOOFDAYS <= 8 
	BEGIN
		-- GETTING CURRENT DATE START TIME.
		SELECT @STARTDATE = DATEADD(DD, DATEDIFF(DD,0,GETDATE()), 0) 
		SET @NOOFDAYS = @NOOFDAYS+1
		-- GETTING THE END TIME OF THE EACH DAY. 
		SELECT @ENDDATE = DATEADD(MS,-3,(DATEADD(DD, DATEDIFF(DD,0,GETDATE())+ @NOOFDAYS, 0))) 
		SELECT @QTY_IN = ISNULL(SUM(QTY_IN), 0) FROM #RESULT_TABLE WHERE CONVERT(VARCHAR,EXPECTED_DATE_OUT,101) BETWEEN @STARTDATE AND @ENDDATE --group by STARTDATE,ENDDATE
		INSERT INTO @FINAL_RESULT_TABLE ([QTY IN],[START DATE],[END DATE]) VALUES ( @QTY_IN, @STARTDATE, @ENDDATE )
	END
	
	SELECT * FROM @FINAL_RESULT_TABLE
	DROP TABLE #RESULT_TABLE
	
END
